﻿using System.Collections.Generic;
using System.Data.Common;
using System.Data;
using MyFloridaHouse.DataAccess.Extensions;
using MyFloridaHouse.libSvDTO;
using System.Data.SqlClient;
using MyFloridaHouse.LibDTO;

namespace MyFloridaHouse.DataAccess
{
	/// <summary>
	/// Data Access Layer to SQL Census Data Store
	/// </summary>
    public partial class DAL_CensusData
    {



        /// <summary>
        /// Gets the census geometry from the database.
        /// Returns a collection of pointers (ID  fields) to layer data as a list of: <see cref="CensusGeometryDTO"/>.
        /// </summary>
        /// <returns>List of <see cref="CensusGeometryDTO"/></returns>
        /// <remarks>SPROC: usp_GetCensusGeometry</remarks>
        public List<ShapeDTO> GetCountyShapes()
        {
            List<ShapeDTO> itemList = new List<ShapeDTO>();

            //string storedProcedureName = "usp_GetCensusGeometry";
            string storedProcedureName = "usp_tl10GetCountyShapes10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                StoredProcedureCommand.CommandTimeout = 60000;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(ShapeDTODataContract.DataContract(rdr));
                    }
                }
            }

            return itemList;
        }




		/// <summary>
		/// Gets the current config key.
		/// </summary>
		/// <param name="iFileName">Name of the i file.</param>
		/// <returns>string</returns>
		/// <remarks>SPROC: usp_GetCurrentConfigKey</remarks>
		public string GetCurrentConfigKey(string iFileName)
        {
            string currentKey="";

            //string storedProcedureName = "usp_GetCurrentConfigKey";
            string storedProcedureName = "usp_tl10GetCurrentConfigKey10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("fileName", iFileName);
                dataPartIDParm.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        currentKey = rdr.GetString(0);
                    }
                }
            }


            //Database db = Connect();
            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_GetCurrentConfigKey");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetCurrentConfigKey10");
            //db.AddInParameter(dbCommand, "fileName", DbType.String, iFileName);
            //dbCommand.CommandTimeout = 60000;
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        currentKey = rdr.GetString(0);
            //    }
            //}
            return currentKey;
        }

		/// <summary>
		/// Gets the census geometry from the database.
		/// Returns a collection of pointers (ID  fields) to layer data as a list of: <see cref="CensusGeometryDTO"/>.
		/// </summary>
		/// <returns>List of <see cref="CensusGeometryDTO"/></returns>
		/// <remarks>SPROC: usp_GetCensusGeometry</remarks>
        public List<CensusGeometryDTO> GetCensusGeometry()
        {
            List<CensusGeometryDTO> itemList = new List<CensusGeometryDTO>();

            //string storedProcedureName = "usp_GetCensusGeometry";
            string storedProcedureName = "usp_tl10GetCensusGeometry10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                StoredProcedureCommand.CommandTimeout = 60000;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read() )
                    {
                        itemList.Add(CensusGeometryDTODataContract.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_GetCensusGeometry");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetCensusGeometry10");
            //dbCommand.CommandTimeout = 60000;
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(CensusGeometryDTODataContract.DataContract(rdr));
            //    }
            //}
            return itemList;
        }


		/// <summary>
		/// Gets the shape uogIDs (same as regionIDs).
		/// </summary>
		/// <param name="iWkt">The iWKT (well known text).</param>
		/// <param name="iLayerID">The ilayerID.</param>
		/// <returns>List of <see cref="ShapeUogIDDTO"/></returns>
		/// <remarks>SPROC: usp_GetShapeUogIDs</remarks>
        public List<ShapeUogIDDTO> GetShapeUogIDs(string iWkt, int iLayerID)
        {
            List<ShapeUogIDDTO> itemList = new List<ShapeUogIDDTO>();

            //string storedProcedureName = "usp_GetShapeUogIDs";
            string storedProcedureName = "usp_tl10GetShapeUogIDs10";

            using (SqlConnection db = Connect())
            {
                SqlCommand StoredProcedureCommand = new SqlCommand(storedProcedureName, db);
                StoredProcedureCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter dataPartIDParm = StoredProcedureCommand.Parameters.AddWithValue("wkt", iWkt);
                dataPartIDParm.Direction = ParameterDirection.Input;
                SqlParameter dataPartIDParm2 = StoredProcedureCommand.Parameters.AddWithValue("layerID", iLayerID);
                dataPartIDParm2.Direction = ParameterDirection.Input;

                db.Open();
                using (SqlDataReader rdr = StoredProcedureCommand.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        itemList.Add(ShapeUogID.DataContract(rdr));
                    }
                }
            }

            //Database db = Connect();
            ////DbCommand dbCommand = db.GetStoredProcCommand("usp_GetShapeUogIDs");
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_tl10GetShapeUogIDs10");
            //db.AddInParameter(dbCommand, "wkt", DbType.String, iWkt);
            //db.AddInParameter(dbCommand, "layerID", DbType.String, iLayerID);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(ShapeUogID.DataContract(rdr));
            //    }
            //}
            return itemList;
        }

		//Todo: Remove obsolete code.
		#region Unused Code

		/// <summary>
		/// [Obsolete] Gets the census geometry info.
		/// </summary>
		/// <returns></returns>
		/// [Obsolete] <remarks>SPROC: usp_GetCensusGeometryInfo</remarks>
		public int GetCensusGeometryInfo()
		{
			int itemRecordCount = 0;

            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_GetCensusGeometryInfo");
            //dbCommand.CommandTimeout = 60000;
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemRecordCount = rdr.GetInt32(0);
            //    }
            //}
			return itemRecordCount;
		}

		/// <summary>
		/// [Obsolete] Gets the geography list from uogids.
		/// </summary>
		/// <returns></returns>
		/// [Obsolete] <remarks>SPROC: usp_GeographyListFromUogids</remarks>
		public List<GeographyListDTO> GetGeographyListFromUogids() //do we need this?
        {
            List<GeographyListDTO> itemList = new List<GeographyListDTO>();


            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_GeographyListFromUogids");
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(GeographyListDTODataContract.DataContract(rdr));
            //    }
            //}
            return itemList;
        }

		/// <summary>
		/// [Obsolete] Gets the district uogid list.
		/// </summary>
		/// <param name="iDistrictId">The i district id.</param>
		/// <returns></returns>
		/// [Obsolete] <remarks>SPROC: usp_GetDistrictUogidList</remarks>
		public List<DistrictGeographyDTO> GetDistrictUogidList(int iDistrictId)
        {
            List<DistrictGeographyDTO> itemList = new List<DistrictGeographyDTO>();

            //Database db = Connect();
            //DbCommand dbCommand = db.GetStoredProcCommand("usp_GetDistrictUogidList");
            //db.AddInParameter(dbCommand, "districtID", DbType.String, iDistrictId);
            //using (IDataReader rdr = db.ExecuteReader(dbCommand))
            //{
            //    while (rdr.Read())
            //    {
            //        itemList.Add(DistrictGeographyDTODataContract.DataContract(rdr));
            //    }
            //}
            return itemList;
		}

		#endregion

	}
}
